/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.sgs.dao;

import br.com.sgs.entity.Departamento;
import br.com.sgs.entity.Solicitacao;
import java.util.Date;
import java.util.List;
import javax.persistence.Query;

/**
 *
 * @author Eric
 */
public class DAOSolicitacao extends AbstractDAO implements IDAOSolicitacao{
    
    public DAOSolicitacao(){
        super();
    }
    
    @Override
    public Solicitacao findSolicitacaoById(int idsolicitacao) {
         try {

            Query q = em.createQuery("SELECT s FROM Solicitacao s WHERE s.id = :idsolicitacao");
            q.setParameter("idsolicitacao", idsolicitacao);
            return (Solicitacao) q.getSingleResult();

        } catch (Exception e) {
            System.err.println("Erro no findSolicitacaoById");
            return null;
        }
    }

    @Override
    public List<Solicitacao> findSolicitacao() {
                   
            Query q = em.createQuery("SELECT s FROM Solicitacao s");
            return q.getResultList();
    }

    @Override
    public List<Solicitacao> findSolicitacaoByAll(String descricao, Date dtcriacaoini, Date dtcriacaofin, List<String> lsstatus, int idaluno, Departamento departamento) {
        
        try {

            StringBuilder sql = new StringBuilder();

            if (!descricao.equals("")) {
                sql.append("  AND UPPER(s.modeloSolicitacao.descricao) LIKE '%").append(descricao.toUpperCase()).append("%'");
            }

            if( dtcriacaoini != null && dtcriacaofin != null ){
                sql.append(" AND s.dtcriacao BETWEEN :dtcriacaoini AND :dtcriacaofin ") ;
            }
            
            if (idaluno > 0) {
                sql.append(" AND s.aluno.id = ").append(idaluno).append("");
            }
            
            if (departamento != null) {
                sql.append(" AND s.etapaatual.departamento = :departamento");
            }
         
            if (!lsstatus.isEmpty()) {
                String status = "";
                for (String st : lsstatus) {
                    status += st + ",";
                }
                status += lsstatus.get(0).toString();
                sql.append(" AND s.status  IN (").append(status).append(")");
            }
            
            Query q = em.createQuery("SELECT s FROM Solicitacao s WHERE s.id > 0 " + sql.toString() + " ORDER BY s.dtcriacao DESC");
            

            if( dtcriacaoini != null && dtcriacaofin != null ){
                q.setParameter("dtcriacaoini", dtcriacaoini);
                q.setParameter("dtcriacaofin", dtcriacaofin);
            }
            
            if (departamento != null){
                q.setParameter("departamento", departamento);
            }
            
            List retorno = q.getResultList();
            
            em.close();
            
            return retorno;
            
            
        } catch (Exception e) {
            System.err.println("Erro na consulta findSolicitacaoByAll");
            e.printStackTrace();
            return null;
        }
        
    }

}
